﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreOracle;
using System.Data.OracleClient;
using System.Data;
using System.Globalization;
using CoreCommon;

namespace gte
{
    public class LogOnOracle : LogOnBase
    {
        public override void ChangePassword(string password)
        {
            string sql = "modern.prk_tab.exp_change_password";
            try
            {
                DB.Item.StartTransaction();
                OracleCommand cmd = new OracleCommand(sql, DB.Item.Connection, DB.Item.Transaction);
                cmd.CommandType = CommandType.StoredProcedure;
                DB.AddInParameter("a_id", OracleType.Number, base.Id, cmd, false);
                DB.AddInParameter("a_password", OracleType.NVarChar, password, cmd, true);

                cmd.ExecuteNonQuery();

                DB.Item.Commit();
            }
            catch
            {
                DB.Item.Rollback();
                throw;
            }

        }

        public override bool CheckPassword(string password)
        {
            string sql = "begin :res := modern.prk_tab.exp_check_password(:a_id, :a_password);end;";
            OracleParameter prmRes;
            try
            {
                DB.Item.StartTransaction();
                OracleCommand cmd = new OracleCommand(sql, DB.Item.Connection, DB.Item.Transaction);
                cmd.CommandType = CommandType.Text;

                DB.AddInParameter("a_id", OracleType.Number, base.Id, cmd, false);
                DB.AddInParameter("a_password", OracleType.NVarChar, password, cmd, false);
                prmRes = DB.AddOutParameter("res", OracleType.Number, cmd);

                cmd.ExecuteNonQuery();
                DB.Item.Commit();
            }
            catch
            {
                DB.Item.Rollback();
                throw;
            }

            if (Convert.ToInt32(prmRes.Value, CultureInfo.InvariantCulture) == 1)
                return true;
            else
                return false;

        }

        public override void Enter(string name, string password, string server)
        {
            Dictionary<string, string> dic = CoreCommon.Log.ReadConfig();
            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder();
            builder.DataSource = dic["Server"].ToString();
            builder.UserID = dic["Login"].ToString();
            builder.Password = dic["Password"].ToString();

            OracleConnection connect = new OracleConnection(builder.ConnectionString);
            connect.Open();
            try
            {
                OracleCommand cmd = new OracleCommand("auth.logon", connect);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("a_user_name", OracleType.NVarChar);
                cmd.Parameters["a_user_name"].Value = name;

                cmd.Parameters.Add("a_user_password", OracleType.NVarChar);
                cmd.Parameters["a_user_password"].Value = password;

                cmd.Parameters.Add("name_out", OracleType.NVarChar, 50);
                cmd.Parameters["name_out"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("passwors_out", OracleType.NVarChar, 20);
                cmd.Parameters["passwors_out"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("a_user_id_out", OracleType.Number);
                cmd.Parameters["a_user_id_out"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("a_user_name_out", OracleType.NVarChar, 50);
                cmd.Parameters["a_user_name_out"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("a_user_login_out", OracleType.NVarChar, 20);
                cmd.Parameters["a_user_login_out"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                base.Id = Convert.ToInt32(cmd.Parameters["a_user_id_out"].Value, CultureInfo.InvariantCulture);
                base.LogOn = cmd.Parameters["a_user_login_out"].Value.ToString();
                base.Name = cmd.Parameters["a_user_name_out"].Value.ToString();

                CoreOracle.DB.Item.Connect(
                    server,
                    cmd.Parameters["name_out"].Value.ToString(),
                    cmd.Parameters["passwors_out"].Value.ToString());

                // Получаем таблицу с разрешениями для пользователя
                string sql =
                    " select func_id from (select ar.func_id, 1 perm from modern.roles_expert rle, modern.action_roles ar" +
                    " where ar.role_id = rle.role_id and rle.expert_id = :expert_id union all select re.func_id, re.permission perm" +
                    " from modern.action_expet re where expert_id = :expert_id) group by func_id having sum(perm) > 0";

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("expert_id", OracleType.Number);
                cmd.Parameters["expert_id"].Value = base.Id;

                base.HashSet.Clear();
                using (OracleDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                        base.HashSet.Add(Convert.ToInt32(rdr["func_id"], CultureInfo.InvariantCulture));
                }
            }
            finally
            {
                connect.Close();
            }
        }

        public override void ReEnter(string name, string password, string server)
        {
            OracleCommand cmd = new OracleCommand("auth.logon", CoreOracle.DB.Item.Connection, CoreOracle.DB.Item.Transaction);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("a_user_name", OracleType.NVarChar);
            cmd.Parameters["a_user_name"].Value = name;

            cmd.Parameters.Add("a_user_password", OracleType.NVarChar);
            cmd.Parameters["a_user_password"].Value = password;

            cmd.Parameters.Add("name_out", OracleType.NVarChar, 50);
            cmd.Parameters["name_out"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add("passwors_out", OracleType.NVarChar, 20);
            cmd.Parameters["passwors_out"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add("a_user_id_out", OracleType.Number);
            cmd.Parameters["a_user_id_out"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add("a_user_name_out", OracleType.NVarChar, 50);
            cmd.Parameters["a_user_name_out"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add("a_user_login_out", OracleType.NVarChar, 20);
            cmd.Parameters["a_user_login_out"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            base.Id = Convert.ToInt32(cmd.Parameters["a_user_id_out"].Value, CultureInfo.InvariantCulture);
            base.LogOn = cmd.Parameters["a_user_login_out"].Value.ToString();
            base.Name = cmd.Parameters["a_user_name_out"].Value.ToString();

            // Получаем таблицу с разрешениями для пользователя
            string sql =
                " select func_id from (select ar.func_id, 1 perm from modern.roles_expert rle, modern.action_roles ar" +
                " where ar.role_id = rle.role_id and rle.expert_id = :expert_id union all select re.func_id, re.permission perm" +
                " from modern.action_expet re where expert_id = :expert_id) group by func_id having sum(perm) > 0";

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.Add("expert_id", OracleType.Number);
            cmd.Parameters["expert_id"].Value = base.Id;

            base.HashSet.Clear();
            using (OracleDataReader rdr = cmd.ExecuteReader())
                while (rdr.Read())
                    base.HashSet.Add(Convert.ToInt32(rdr["func_id"], CultureInfo.InvariantCulture));

        }
    }
}
